package com.iu.dao;

import com.iu.bean.NoteBean;
import com.iu.uitl.DBUtil;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NoteDao {
    Connection connection;

    /*插入备忘记录*/
    public boolean insertNote(NoteBean noteBean) {
        try {
            connection = DBUtil.getConnection();
            PreparedStatement statement = connection.prepareStatement("insert into note_table(title,content,create_time,note_time ,user_id)values(?,?,?,?,?)");
            statement.setString(1, noteBean.getTitle());
            statement.setString(2, noteBean.getContent());
            statement.setString(3, noteBean.getCreateTime());
            statement.setString(4, noteBean.getNoteTime());
            statement.setInt(5, noteBean.getUserId());
            statement.execute();
            DBUtil.closeResources(connection, statement, null);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return false;
    }
    /*根据id查询备忘记录*/
    public NoteBean getNoteById(int id) {
        connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT * from note_table WHERE id=?");
            preparedStatement.setInt(1, id);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                NoteBean noteBean = new NoteBean();
                noteBean.setId(resultSet.getInt(1));
                noteBean.setTitle(resultSet.getString(2));
                noteBean.setContent(resultSet.getString(3));
                noteBean.setCreateTime(resultSet.getString(4));
                noteBean.setUpdateTime(resultSet.getString(5));
                noteBean.setNoteTime(resultSet.getString(6));
                DBUtil.closeResources(connection,preparedStatement,null);
                return noteBean;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    /*查询所有备忘记录*/
    public List<NoteBean> queryNote(int userId) {
        List<NoteBean> list = new ArrayList<>();//思考？ 是先声明再赋值优，还是直接定义同时赋值好 应该是一样的，属性有默认值，局部变量没有
        connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("SELECT * from note_table WHERE user_id=?");
            preparedStatement.setInt(1, userId);
            ResultSet resultSet = preparedStatement.executeQuery();
            while (resultSet.next()) {
                NoteBean noteBean = new NoteBean();
                noteBean.setId(resultSet.getInt(1));
                noteBean.setTitle(resultSet.getString(2));//第一行是id，所以从第二行开始
                noteBean.setContent(resultSet.getString(3));
                noteBean.setCreateTime(resultSet.getString(4));
                noteBean.setUpdateTime(resultSet.getString(5));
                noteBean.setNoteTime(resultSet.getString(6));
                list.add(noteBean);
            }
            DBUtil.closeResources(connection,preparedStatement,null);
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }
    /*根据备忘id修改备忘内容*/
    public boolean updateNote(int id ,String title,String content,String noteTime){
        if (id<0)
            return false;
        connection=DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement=connection.prepareStatement("UPDATE note_table SET title=?,content=?,update_time=?,note_time=? WHERE id=?");
            //倘若修改一个或者若干个，则需要用到if语句拼接
            preparedStatement.setString(1,title);
            preparedStatement.setString(2,content);
            preparedStatement.setString(3,dataToString(new Date()));
            preparedStatement.setString(4,noteTime);
            preparedStatement.setInt(5,id);
            preparedStatement.execute();
            DBUtil.closeResources(connection,preparedStatement,null);
            return true;
        } catch (SQLException e) {
            e.printStackTrace();
        }

        return false;
    }
    /*根据id删除备忘记录*/
    public boolean deleteById(int id) {
        connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement("DELETE from note_table WHERE id=?");
            preparedStatement.setInt(1, id);
           preparedStatement.execute();
           DBUtil.closeResources(connection,preparedStatement,null);
           return true;
        }catch (Exception e){
            e.printStackTrace();
        }
        return false;
    }

    /*时间转日期格式化方法*/
    public String dataToString(Date date) {
        String strData = null;
        try {
            SimpleDateFormat simpleDateFormat = new SimpleDateFormat("yyyy年MM月dd日 HH:mm:ss");
            strData = simpleDateFormat.format(date);
        } catch (Exception e) {
            e.printStackTrace();
        }

        return strData;

    }
}
